package data;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class TestUserSelection {

	/**
	 * @param args
	 */

	private Connection conn;

	public TestUserSelection() throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String url = "jdbc:mysql://localhost:3306/" + "bibsonomy";
		String dbuser = "root";
		String dbpass = "123456";
		Connection con = (Connection) DriverManager.getConnection(url, dbuser,
				dbpass);
		this.conn = con;
	}

	public void select() throws Exception {

		Statement stmt = null;
		ResultSet rs = null;
		String path = "D:\\CityU\\project\\bibsonomy\\" + "\\testUsers.txt";
		BufferedWriter writer = new BufferedWriter(new FileWriter(path));

		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("select user, count(distinct(content_id)) as cnt from tas_new group by user having cnt>=50 and cnt<=500 order by cnt desc;");
			int c = 0;
			while (rs.next()) {
				String user = rs.getString("user");
				String cnt = rs.getString("cnt");
                writer.write(user + "\t" + cnt);
                writer.newLine();
                c++;
			}
			System.out.println("total testuser num\t" + c);
		} finally {
			writer.flush();
			writer.close();
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}

	}
	
	public static List<String> retrieveTestUsers() throws Exception{
		List<String> set = new ArrayList<String>();
		String path = "D:\\CityU\\project\\bibsonomy\\" + "\\testUsers.txt";
		BufferedReader reader = new BufferedReader(new FileReader(path));
		String line = null;
		while((line = reader.readLine()) != null) {
			line = line.trim();
			String[] tokens = line.split("\t");
			String user = tokens[0];
			set.add(user);
		}		
		reader.close();
		return set;
	}

	public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
        TestUserSelection app = new TestUserSelection();
        app.select();
        
	}

}
